package com.dglg.spring.controller;


import com.dglg.spring.dao.ExcelDao;
import com.dglg.spring.model.Student;
import com.dglg.spring.model.TrainingStudent;
import com.dglg.spring.service.ExcelService;
import org.apache.ibatis.annotations.Param;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@RestController
public class ExcelController {

    List<Student> studentslist = new ArrayList<>();
    List<String> idlist = new ArrayList<>();
    List<TrainingStudent> trainingStudentList = new ArrayList<>();
    @Autowired
    private ExcelService excelService;
    @Autowired
    private ExcelDao excelDao;

    @PostMapping("/import")
    public Result importExcel(@RequestParam("file") MultipartFile file, @RequestParam("className") String className) throws IOException {
        Sheet sheet = null;
        // 获取文件后缀名
        String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));

        // 判断文件后缀名的格式
        if(type.equals(".xlsx")) {
            // 高版本的excel
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
           sheet = workbook.getSheetAt(0);
        } else if(type.equals(".xls")) {
            // 低版本的excel
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());  // excel文件转成wb对象
            sheet = workbook.getSheetAt(0);
        } else {
            // 非excel格式，需重新下载模板
            Integer code = Code.GET_ERR;
            String msg = "文件格式不匹配，请重新下载excel模板！";

            return new Result(code, 0, msg);
        }
        Row title = sheet.getRow(0);
        // 获取标题行
        String[] templates = {"student_id", "student_name", "gender", "grade", "class", "major"};
        for (int i = 0; i < title.getPhysicalNumberOfCells(); i++) {
            // 判断模板的标题行与数据库表属性是否一致，若不一致，重新下载模板
            if (!templates[i].equals(title.getCell(i).getStringCellValue())) {
                Integer code = Code.GET_ERR;
                String msg = "数据模板不匹配，请重新下载模板！";

                return new Result(code, 0, msg);
            }
        }

        int rowNum = sheet.getPhysicalNumberOfRows();
        Row row = null;
        // 获取第i行数据
        for (int i = 1; i < rowNum; i++) {
            Student student = new Student();
            row = sheet.getRow(i);
            // 获取第i行第j列的数据
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                Cell cell = row.getCell(j);
                // 将获取到的学生信息封装进学生类中
                student = excelService.student(j, String.valueOf(cell), student);
            }
            // 把获取到的每一个完整的学生信息添加进学生信息列表
            studentslist.add(student);
        }
        String result = null;
        // 通过列表导入学生信息
        result = String.valueOf(excelDao.addStudent(studentslist));

        idlist = excelService.getStudentIdByMajor(className);
        System.out.println(idlist);

        for(String id : idlist) {
            TrainingStudent trainingStudent = new TrainingStudent();
            trainingStudent.setStudentId(id);
            trainingStudent.setClassName(className);
            trainingStudentList.add(trainingStudent);
        }

        result = String.valueOf(excelDao.addTrainingStu(trainingStudentList));

        Integer code = result != null ? Code.GET_OK : Code.GET_ERR;
        String msg = result != null ? "数据导入成功！" : "数据导入失败，请重试！";

        return new Result(code, result, msg);
    }
}
